package com.bookstore.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bookstore.model.Book;
import com.bookstore.model.Product;
import com.bookstore.utils.DBConnection;
import com.mysql.jdbc.Connection;

public class ProductDAOImpl implements ProductDAO {

	private final static String FIND_BY_ID = "select p.*, b.* from d_product p join d_book b on p.id=b.id where p.id=?";
	private final static String FIND_BY_HOT = "select sum(product_num) as num, p.*, b.* from d_item i join d_product p on i.product_id=p.id join d_book b on i.product_id=b.id group by product_id order by num desc limit 0, ?";
	private final static String FIND_BY_NEW = "SELECT p.*,b.* FROM d_product p JOIN d_book b ON p.id=b.id ORDER BY p.add_time DESC LIMIT 0,?";

	@Override
	public Product findById(int id) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Book book = null;
		try {
			statement = DBConnection.getConnection().prepareStatement(FIND_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				book = new Book();
				book.setId(resultSet.getInt("id"));
				book.setProductName(resultSet.getString("product_name"));
				book.setFixedPrice(resultSet.getDouble("fixed_price"));
				book.setDangPrice(resultSet.getDouble("dang_price"));
				book.setProductPic(resultSet.getString("product_pic"));
				book.setAddTime(resultSet.getLong("add_time"));
				book.setAuthor(resultSet.getString("author"));
				book.setPublishing(resultSet.getString("publishing"));
				book.setPublishTime(resultSet.getLong("publish_time"));
				book.setAuthorSummary(resultSet.getString("author_summary"));
				book.setCatalogue(resultSet.getString("catalogue"));
				book.setDescription(resultSet.getString("description"));
				book.setHasDeleted(resultSet.getInt("has_deleted"));
				book.setIsbn(resultSet.getString("isbn"));
				book.setKeyWords(resultSet.getString("keywords"));
				book.setPrintNumber(resultSet.getString("print_number"));
				book.setPrintTime(resultSet.getLong("print_time"));
				book.setPublishing(resultSet.getString("publishing"));
				book.setTotalPage(resultSet.getString("total_page"));
				book.setWhichEdtion(resultSet.getString("which_edtion"));
				book.setWordNumber(resultSet.getString("word_number"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(resultSet, statement, connection);
		}
		return book;
	}

	@Override
	public List<Product> findByHot(int num) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Product> list = new ArrayList<>();
		try {
			statement = DBConnection.getConnection().prepareStatement(FIND_BY_HOT);
			statement.setInt(1, num);
			resultSet = statement.executeQuery();
			Book book = null;
			while (resultSet.next()) {
				book = new Book();
				book.setId(resultSet.getInt("id"));
				book.setProductName(resultSet.getString("product_name"));
				book.setFixedPrice(resultSet.getDouble("fixed_price"));
				book.setDangPrice(resultSet.getDouble("dang_price"));
				book.setProductPic(resultSet.getString("product_pic"));
				book.setAddTime(resultSet.getLong("add_time"));
				book.setAuthor(resultSet.getString("author"));
				book.setPublishing(resultSet.getString("publishing"));
				book.setPublishTime(resultSet.getLong("publish_time"));
				book.setAuthorSummary(resultSet.getString("author_summary"));
				book.setCatalogue(resultSet.getString("catalogue"));
				book.setDescription(resultSet.getString("description"));
				book.setHasDeleted(resultSet.getInt("has_deleted"));
				book.setIsbn(resultSet.getString("isbn"));
				book.setKeyWords(resultSet.getString("keywords"));
				book.setPrintNumber(resultSet.getString("print_number"));
				book.setPrintTime(resultSet.getLong("print_time"));
				book.setPublishing(resultSet.getString("publishing"));
				book.setTotalPage(resultSet.getString("total_page"));
				book.setWhichEdtion(resultSet.getString("which_edtion"));
				book.setWordNumber(resultSet.getString("word_number"));
				list.add(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(resultSet, statement, connection);
		}
		return list;
	}

	@Override
	public List<Product> findByNew(int num) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Product> list = new ArrayList<>();
		try {
			statement = DBConnection.getConnection().prepareStatement(FIND_BY_NEW);
			statement.setInt(1, num);
			resultSet = statement.executeQuery();
			Book book = null;
			while (resultSet.next()) {
				book = new Book();
				book.setId(resultSet.getInt("id"));
				book.setProductName(resultSet.getString("product_name"));
				book.setFixedPrice(resultSet.getDouble("fixed_price"));
				book.setDangPrice(resultSet.getDouble("dang_price"));
				book.setProductPic(resultSet.getString("product_pic"));
				book.setAddTime(resultSet.getLong("add_time"));
				book.setAuthor(resultSet.getString("author"));
				book.setPublishing(resultSet.getString("publishing"));
				book.setPublishTime(resultSet.getLong("publish_time"));
				book.setAuthorSummary(resultSet.getString("author_summary"));
				book.setCatalogue(resultSet.getString("catalogue"));
				book.setDescription(resultSet.getString("description"));
				book.setHasDeleted(resultSet.getInt("has_deleted"));
				book.setIsbn(resultSet.getString("isbn"));
				book.setKeyWords(resultSet.getString("keywords"));
				book.setPrintNumber(resultSet.getString("print_number"));
				book.setPrintTime(resultSet.getLong("print_time"));
				book.setPublishing(resultSet.getString("publishing"));
				book.setTotalPage(resultSet.getString("total_page"));
				book.setWhichEdtion(resultSet.getString("which_edtion"));
				book.setWordNumber(resultSet.getString("word_number"));
				list.add(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(resultSet, statement, connection);
		}
		return list;
	}

}
